define(['sql-formatter', 'js/factories/util'], function(sqlFormatter) {
    var sqlParserFactory = angular.module('app.factory.sqlParser', ['app.factory.util']);

    sqlParserFactory.factory('xSqlParser', ['xUtil', function(xUtil) {
        function toJavaName(name) {
            if (name == name.toUpperCase()) {
                name = name.toLowerCase();
            }
            var names = name.split(/\-+|_+/);
            var javaName = '';
            if (names.length == 1) {
                javaName = name.substring(0, 1).toLowerCase() + name.substring(1);
            } else {
                javaName = names[0].toLowerCase();
                for (var i = 1; i < names.length; i++) {
                    javaName += names[i].substring(0, 1).toUpperCase() + names[i].substring(1);
                }
            }
            return javaName;
        }

        return {
            parse: function(sql, typeReg, fnCustomizedType) {
                const tableReg = /CREATE TABLE ([\w`"\.]+)[^\(]*\(([\s\S]+)\)/gmi;
                const columnReg = /\s*([0-9A-Za-z_`"]+)\s+([0-9A-Za-z_]+)\s*(\((\d+(\s*,\s*\d+)?)\))?/;
                const columnCommentReg = /COMMENT\s+'([^']+)'/i;

                if (typeReg) {
                    // save type matcher
                    var typePlaceholders = [];
                    sql = sql.replace(typeReg, function(match) {
                        typePlaceholders.push(match);
                        return '`TYPE_PLACEHOLDER_' + (typePlaceholders.length - 1) + '`';
                    });
                    sql = sqlFormatter.format(sql, { language: 'sql' });
                    _.forEach(typePlaceholders, function(typePlaceholder, i) {
                        sql = sql.replace('`TYPE_PLACEHOLDER_' + i + '`', typePlaceholder);
                    });
                } else {
                    sql = sqlFormatter.format(sql, { language: 'sql' });
                }

                var schema = null;

                var match = tableReg.exec(sql);
                if (match && match.length > 2) {
                    schema = { table: {}, columns: [] };
                    // table
                    schema.table['name'] = match[1].replace(/[^\w\.]/g, '');
                    // columns
                    var columnDefs = match[2].split(/\n/);
                    _.forEach(columnDefs, function(columnDef) {
                        if (!/^\s+(PRIMARY|UNIQUE|CONSTRAINT|FOREIGN)\s+KEY/i.test(columnDef)
                                && !/^\s+(UNIQUE\s+)?INDEX/i.test(columnDef)) {
                            match = columnReg.exec(columnDef);
                            if (match && match.length > 2) {
                                var name = match[1].replace(/[^\w]/g, '');
                                var column = {
                                    name: name,
                                    type: '',
                                    args: [],
                                    comment: xUtil.regex.subMatch(columnDef, columnCommentReg).trim(),
                                    jdbcType: '',
                                    javaName: toJavaName(name),
                                    javaType: ''
                                };
                                // primary key
                                if (/PRIMARY\s+KEY/i.test(columnDef)) {
                                    column.primaryKey = true;
                                }
                                // origin type
                                if (!column.type) {
                                    column.type = match[2];
                                }
                                column.type = column.type.toUpperCase();
                                if (match.length > 4 && match[4]) {
                                    column.args = _.map(match[4].split(/,/g), function(arg) {
                                        return parseInt(arg);
                                    });
                                }
                                // customized type
                                if (typeof fnCustomizedType === 'function') {
                                    fnCustomizedType(column, columnDef);
                                }
                                if (!column.jdbcType || !column.javaType) {
                                    // jdbc type
                                    switch(column.type) {
                                        case 'VARCHAR2':
                                        case 'TEXT':
                                        case 'LONGTEXT':
                                            column.jdbcType = 'VARCHAR';
                                            break;
                                        case 'INT':
                                            column.jdbcType = 'INTEGER';
                                            break;
                                        default:
                                            column.jdbcType = column.type;
                                            break;
                                    }
                                    // java type
                                    if (!column.javaType) {
                                        switch(column.jdbcType) {
                                            case 'BIT':
                                            case 'BOOLEAN':
                                                column.javaType = 'Boolean';
                                                break;
                                            case 'CHAR':
                                            case 'VARCHAR':
                                            case 'LONGVARCHAR':
                                                column.javaType = 'String';
                                                break;
                                            case 'TINYINT':
                                                if (column.args.length > 0 && column.args[0] == 1) {
                                                    column.javaType = 'Boolean';
                                                    break;
                                                }
                                            case 'NUMBER':
                                                if (column.args.length > 1 && column.args[1] > 0) {
                                                    column.javaType = 'Double';
                                                    break;
                                                }
                                            case 'SMALLINT':
                                            case 'MEDIUMINT':
                                            case 'INTEGER':
                                                column.javaType = 'Integer';
                                                break;
                                            case 'BIGINT':
                                                column.javaType = 'Long';
                                                break;
                                            case 'FLOAT':
                                                column.javaType = 'Float';
                                                break;
                                            case 'DOUBLE':
                                                column.javaType = 'Double';
                                            case 'NUMERIC':
                                            case 'DECIMAL':
                                                column.javaType = 'BigDecimal';
                                                break;
                                                break;
                                            case 'DATE':
                                            case 'TIME':
                                            case 'DATETIME':
                                            case 'TIMESTAMP':
                                                column.javaType = 'Date';
                                                break;
                                            default:
                                                column.javaType = 'Object';
                                                break;
                                        }
                                    }
                                }
                                schema.columns.push(column);
                            }
                        }

                        // primary key
                        var keyMatch = null;
                        if ((keyMatch = columnDef.match(/^\s+PRIMARY\s+KEY\s+\((.+)\)/i))) {
                            var rcolumn = /([\w`"]+),?/g,
                                columnMatch = null,
                                primaryKeys = [];
                            while ((columnMatch = rcolumn.exec(keyMatch[1]))) {
                                primaryKeys.push(columnMatch[1].replace(/[^\w]/g, ''));
                            }

                            // match primary key
                            _.forEach(primaryKeys, function(primaryKey) {
                                _.forEach(schema.columns, function(column) {
                                    if (column.name == primaryKey) {
                                        column.primaryKey = true;
                                    }
                                });
                            });
                        }
                    });
                }

                return schema;
            }
        }
    }]);

    return sqlParserFactory;
});
